package cn.easybuy.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.easybuy.dao.BaseDao;
import cn.easybuy.dao.ProductDao;
import cn.easybuy.entity.OrderDetail;
import cn.easybuy.entity.Product;

public class ProductDaoImpl extends BaseDao implements ProductDao{

	@Override
	public List<Product> getProductsByCategoryLevel3Id(int categoryLevel3Id) {
		String sql="select * from easybuy_product where categoryLevel3Id=?";
		ResultSet rs=this.exxcuteQuery(sql, categoryLevel3Id);
		List<Product> list=new ArrayList<Product>();
		try {
			while(rs.next()){
				Product product=new Product();
				product.setId(rs.getInt(1));
				product.setName(rs.getString(2));
				product.setDescription(rs.getString(3));
				product.setPrice(rs.getDouble(4));
				product.setStock(rs.getInt(5));
				product.setCategoryLevel1Id(rs.getInt(6));
				product.setCategoryLevel2Id(rs.getInt(7));
				product.setCategoryLevel3Id(rs.getInt(8));
				product.setFileName(rs.getString(9));
				product.setIsDelete(rs.getInt(10));
				list.add(product);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		return list;
	}

	@Override
	public List<Product> getProductInfo(int id) {
		String sql="select * from easybuy_product where id=?";
		ResultSet rs=this.exxcuteQuery(sql, id);
		List<Product> list=new ArrayList<Product>();
		try {
			while(rs.next()){
				Product product=new Product();
				product.setId(rs.getInt(1));
				product.setName(rs.getString(2));
				product.setDescription(rs.getString(3));
				product.setPrice(rs.getDouble(4));
				product.setStock(rs.getInt(5));
				product.setCategoryLevel1Id(rs.getInt(6));
				product.setCategoryLevel2Id(rs.getInt(7));
				product.setCategoryLevel3Id(rs.getInt(8));
				product.setFileName(rs.getString(9));
				product.setIsDelete(rs.getInt(10));
				list.add(product);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		return list;
	}

	@Override
	public List<Product> getAllProducts(int currNo, int pageSize) {
		String sql="select * from easybuy_product order by id limit ?,?";
		ResultSet rs=this.exxcuteQuery(sql,(currNo-1)*pageSize,pageSize);
		List<Product> list=new ArrayList<Product>();
		try {
			while(rs.next()){
				Product product=new Product();
				product.setId(rs.getInt(1));
				product.setName(rs.getString(2));
				product.setDescription(rs.getString(3));
				product.setPrice(rs.getDouble(4));
				product.setStock(rs.getInt(5));
				product.setCategoryLevel1Id(rs.getInt(6));
				product.setCategoryLevel2Id(rs.getInt(7));
				product.setCategoryLevel3Id(rs.getInt(8));
				product.setFileName(rs.getString(9));
				product.setIsDelete(rs.getInt(10));
				list.add(product);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		return list;
	}

	@Override
	public int getTotalCount() {
		String sql="select count(1) from easybuy_product";
		ResultSet rs=this.exxcuteQuery(sql);
		int totalCount=0;
		try {
			while (rs.next()) {
				totalCount=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		return totalCount;
	}

	@Override
	public List<Object> getObjectsByOrderId(int orderId) {
		String sql="SELECT `name`,fileName,quantity,`cost` FROM easybuy_product,easybuy_order_detail WHERE easybuy_product.`id`=easybuy_order_detail.`productId` AND easybuy_order_detail.`orderId`=?";
		ResultSet rs=this.exxcuteQuery(sql, orderId);
		List<Object> list=new ArrayList<Object>();
		try {
			while (rs.next()) {
				Product product=new Product();
				OrderDetail orderDetail = new OrderDetail();
				product.setName(rs.getString(1));
				product.setFileName(rs.getString(2));
				orderDetail.setQuantity(rs.getInt(3));
				orderDetail.setCost(rs.getDouble(4));
				list.add(product);
				list.add(orderDetail);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		return list;
	}

	@Override
	public int getLevel1Id(int id) {
		String sql="select categoryLevel1Id from easybuy_product where id = ?";
		int categoryLevel1Id=0;
		ResultSet rs =this.exxcuteQuery(sql, id);
		try {
			while (rs.next()) {
				categoryLevel1Id=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		return categoryLevel1Id;
	}

	@Override
	public int getLevel2Id(int id) {
		String sql="select categoryLevel2Id from easybuy_product where id = ?";
		int categoryLevel2Id=0;
		ResultSet rs =this.exxcuteQuery(sql, id);
		try {
			while (rs.next()) {
				categoryLevel2Id=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		return categoryLevel2Id;
	}

	@Override
	public int getLevel3Id(int id) {
		String sql="select categoryLevel3Id from easybuy_product where id = ?";
		int categoryLevel3Id=0;
		ResultSet rs =this.exxcuteQuery(sql, id);
		try {
			while (rs.next()) {
				categoryLevel3Id=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		return categoryLevel3Id;
	}

	@Override
	public int update(Product product) {
		String sql="update easybuy_product set name=?,description=?,price=?,stock=?,categoryLevel1Id=?,categoryLevel2Id=?,categoryLevel3Id=? where id=?";
		int num=this.executeUpdate(sql, product.getName(),product.getDescription(),product.getPrice(),product.getStock(),product.getCategoryLevel1Id(),product.getCategoryLevel2Id(),product.getCategoryLevel3Id(),product.getId());
		return num;
	}

	@Override
	public int getProIdByName(String name) {
		String sql="select id from easybuy_product where name=?";
		int id=0;
		ResultSet rs=this.exxcuteQuery(sql, name);
		try {
			while (rs.next()) {
				id=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		return id;
	}

	
	
	
}
